MySQL 一對多基本操作

mysql 自學筆記

no027843 2021-01-18 23:57:03 ‧ 10449 瀏覽

分享至

實體

所謂實體即為描述我們真實世界的物件，如員工、客戶、訂單皆屬於實體。
在實務上的需求我們可以將實體轉換成各種資料表(TABLE)，如員工資料表、客戶資料表等。

關係

關係指一個實體在另一個實體之間關聯的方式，分為一對一關係、一對多關係、多對一關係、多對多關係。
基本上，實體與關係是用來將事物模組化，並以圖形表示，稱作ER(Entity-Relationship)圖。

其餘名詞介紹如下圖所示。

ER圖實例如下圖。

鍵屬性

指屬性的值在某環境下具有的唯一性，在實體關係圖我們會在鍵屬性的名稱下加上底線。

主鍵(Primary Key):
關係型資料庫中的一條記錄中有好幾個屬性，若其中某一個屬性組(注意是組)能唯一標識一條記錄，該屬性組就可以成為一個主鍵，不許為空、重複，如身分證字號。

外鍵(Foreign Key):
資料表的外鍵是另一表的主鍵，外鍵可以重複的，可以是空值，有了他我們可以用來和其他表建立聯絡。

FOREIGN KEY (customer_id) REFERENCES customers(id)

在create table時，可以利用來加入外鍵(FK)，並且設置他參照的對象，table_name column_name，而加入外鍵可以用來建立與主鍵(PK)的關聯，並且約束外鍵，如插入非空值時，如果主鍵表中沒有這個值，則不能插入、更新時，不能改為主鍵表中沒有的值等等。

CREATE TABLE orders(
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    amount DECIMAL(8, 2),
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
  );

INNER JOIN可以通過結合基於聯接兩個表(表1和表2)且創建一個新的結果表。

所用之資料

mysql> SELECT * FROM customers;
+----+------------+-----------+----------------+
| id | first_name | last_name | email          |
+----+------------+-----------+----------------+
|  1 | Robin      | Jackman   | roj@gmail.com  |
|  2 | Taylor     | Edward    | taed@gmail.com |
|  3 | Vivian     | Dickens   | vidi@gmail.com |
|  4 | Harley     | Gilbert   | hgi@gmail.com  |
|  5 | jo         | jo        | jojo@gmail.com |
+----+------------+-----------+----------------+

mysql> SELECT * FROM orders;
+----+------------+--------+-------------+
| id | order_date | amount | customer_id |
+----+------------+--------+-------------+
|  1 | 2001-10-12 |  99.12 |           1 |
|  2 | 2001-09-21 | 110.99 |           2 |
|  3 | 2001-10-13 |  12.19 |           1 |
|  4 | 2001-11-29 |  88.09 |           3 |
|  5 | 2001-11-11 | 205.01 |           4 |
|  8 | 2001-12-11 | 100.00 |           4 |
+----+------------+--------+-------------+

必需指定等值連接的條件，而查詢結果只會返回符合連接條件的資料。

SELECT table_column1, table_column2...
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name;

將customers 及orders 結合，並獲取所需之資料，而返回的結果條件為order的customer id需等同於customer的id，並且利用group BY去重複customers.id，並組成一組。

而條件部分的on功能等同於where。

SELECT first_name,last_name, SUM(amount) AS total 
FROM customers 
INNER JOIN orders ON orders.customer_id=customers.id 
GROUP BY customers.id;

+------------+-----------+--------+
| first_name | last_name | total  |
+------------+-----------+--------+
| Robin      | Jackman   | 111.31 |
| Taylor     | Edward    | 110.99 |
| Vivian     | Dickens   |  88.09 |
| Harley     | Gilbert   | 305.01 |
+------------+-----------+--------+

LEFT JOIN

當我們碰到一個情況，在customer中有一個客人是沒有任何訂單，但是我們在join的時候也想要顯示出來，此時就可以使用Left Join，左側資料表 (table_name1) 的所有記錄都會加入到查詢結果中，即使右側資料表 (table_name2) 中的連接欄位沒有符合的值也一樣

SELECT table_column1, table_column2
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;

SELECT * FROM customers left join orders on customers.id = orders.customer_id;
+----+------------+-----------+----------------+------+------------+--------+-------------+
| id | first_name | last_name | email          | id   | order_date | amount | customer_id |
+----+------------+-----------+----------------+------+------------+--------+-------------+
|  1 | Robin      | Jackman   | roj@gmail.com  |    1 | 2001-10-12 |  99.12 |           1 |
|  1 | Robin      | Jackman   | roj@gmail.com  |    3 | 2001-10-13 |  12.19 |           1 |
|  2 | Taylor     | Edward    | taed@gmail.com |    2 | 2001-09-21 | 110.99 |           2 |
|  3 | Vivian     | Dickens   | vidi@gmail.com |    4 | 2001-11-29 |  88.09 |           3 |
|  4 | Harley     | Gilbert   | hgi@gmail.com  |    5 | 2001-11-11 | 205.01 |           4 |
|  4 | Harley     | Gilbert   | hgi@gmail.com  |    8 | 2001-12-11 | 100.00 |           4 |
|  5 | jo         | jo        | jojo@gmail.com | NULL | NULL       |   NULL |        NULL |
+----+------------+-----------+----------------+------+------------+--------+-------------+

如果不想顯示NULL值，可以利用CASE判斷式。

SELECT
  first_name,
  last_name,
  case
    when sum(amount) is NULL THEN 0
    else sum(amount)
  END AS total
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id
group by
  customers.id;

或是利用函數IFNULL，判斷第一個參數是否為NULL，是的話替換成第二個參數的值

SELECT
  first_name,
  last_name,
  IFNULL(SUM(amount), "87jojo") AS total
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id
group by
  customers.id;

RIGHT JOIN

與LEFT JOIN差別在於，LEFT JOIN是以customers table為基礎，而RIGHT JOIN是以order table為基礎。

mysql> SELECT * FROM customers right join orders on customers.id = orders.customer_id;
+------+------------+-----------+----------------+----+------------+--------+-------------+
| id   | first_name | last_name | email          | id | order_date | amount | customer_id |
+------+------------+-----------+----------------+----+------------+--------+-------------+
|    1 | Robin      | Jackman   | roj@gmail.com  |  1 | 2001-10-12 |  99.12 |           1 |
|    1 | Robin      | Jackman   | roj@gmail.com  |  3 | 2001-10-13 |  12.19 |           1 |
|    2 | Taylor     | Edward    | taed@gmail.com |  2 | 2001-09-21 | 110.99 |           2 |
|    3 | Vivian     | Dickens   | vidi@gmail.com |  4 | 2001-11-29 |  88.09 |           3 |
|    4 | Harley     | Gilbert   | hgi@gmail.com  |  5 | 2001-11-11 | 205.01 |           4 |
|    4 | Harley     | Gilbert   | hgi@gmail.com  |  8 | 2001-12-11 | 100.00 |           4 |
+------+------------+-----------+----------------+----+------------+--------+-------------+

JOIN有點類似交集的概念。
如下圖所示

ON DELETE

當我們想要刪除有被其他table的外鍵參照的column時，是無法刪除的，比如此處我們想刪除customers中的資料，但由於orders中有外鍵參照她，所以無法刪除。

CREATE TABLE customers(
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(100) 
);

CREATE TABLE orders(
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    amount DECIMAL(8,2),
    customer_id INT,
    FOREIGN KEY(customer_id)
        REFERENCES customers(id)
);

mysql> DELETE FROM customers where id="1";
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test7`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))

此時，我們只需在創建orders時，加上ON DELETE CASCADE即可。
刪除customers資料的同時，也會將其刪除orders對應的資料。

CREATE TABLE orders(
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    amount DECIMAL(8,2),
    customer_id INT,
    FOREIGN KEY(customer_id)
        REFERENCES customers(id)
        ON DELETE CASCADE
);

mysql> DELETE FROM customers where id="1";
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM customers;
+----+------------+-----------+----------------+
| id | first_name | last_name | email          |
+----+------------+-----------+----------------+
|  2 | Taylor     | Edward    | taed@gmail.com |
|  3 | Vivian     | Dickens   | vidi@gmail.com |
|  4 | Harley     | Gilbert   | hgi@gmail.com  |
+----+------------+-----------+----------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM orders;
+----+------------+--------+-------------+
| id | order_date | amount | customer_id |
+----+------------+--------+-------------+
|  2 | 2001-09-21 | 110.99 |           2 |
|  4 | 2001-11-29 |  88.09 |           3 |
|  5 | 2001-11-11 | 205.01 |           4 |
+----+------------+--------+-------------+
3 rows in set (0.00 sec)